Imports System.Collections.Generic
Imports System.Linq
Imports System.Web

#Region "using"
Imports System.Data
Imports QUANLY.DTO
#End Region
Public Class TOURDAO
	Inherits AbstractDAO
	Public Sub New(strPath As [String])
		MyBase.New(strPath)
	End Sub

	Public Function SelectAll() As DataSet
		Dim ds As DataSet
		Try
			ds = New DataSet()
			MyBase.sql = "usp_TOUR_SelectAll"
			MyBase.FillDataSet(ds, "TOUR")
			Return ds
		Catch e As System.Exception
			Throw New Exception(e.Message, e.InnerException)
		Finally
			MyBase.CleanUpObject()
		End Try
	End Function

	Public Function SelectByID(id As Integer) As DataSet
		Dim ds As DataSet
		Try
			ds = New DataSet()
			MyBase.sql = "usp_TOUR_SelectByID"
			MyBase.InitializeCommand()
			MyBase.AddParameter("@ID", SqlDbType.Int, 1000, id)
			MyBase.FillDataSet(ds, "TOUR")
			Return ds
		Catch e As System.Exception
			Throw New Exception(e.Message, e.InnerException)
		Finally
			MyBase.CleanUpObject()
		End Try
	End Function

    Public Function Search(ByVal id As String, ByVal name As String, ByVal giatu As String, ByVal giaden As String, ByVal ngaybd As DateTime, ByVal ngaykt As DateTime) As DataSet
        Dim ds As DataSet
        Try
            ds = New DataSet()
            MyBase.sql = "SELECT ID, TenTour, GioiThieu, DonGia, NgayBD, NgayKT " & _
                        "FROM TOUR " & _
                        "WHERE ID = " & IIf(id = "", "ID", id) & " And " & _
                            "UPPER(TenTour) like " & IIf(name = "", "UPPER(TenTour)", "N'%" & name.Trim.ToUpper() & "%'") & " And " & _
                            "DonGia >= " & IIf(giatu = "", "DonGia", giatu) & " And " & _
                            "DonGia <= " & IIf(giaden = "", "DonGia", giaden) & " And " & _
                            "NgayBD >= " & IIf(ngaybd = DateTime.MinValue, "NgayBD", "'" & ngaybd & "'") & " And " & _
                            "NgayKT <= " & IIf(ngaykt = DateTime.MinValue, "NgayKT", "'" & ngaykt & "'")
            MyBase.InitializeCommand()
            MyBase.FillDataSet(ds, "TOUR")
            Return ds
        Catch e As System.Exception
            Throw New Exception(e.Message, e.InnerException)
        Finally
            MyBase.CleanUpObject()
        End Try
    End Function

    Public Function ThongKe(ByVal ngaybd As DateTime, ByVal ngaykt As DateTime) As DataSet
        Dim ds As DataSet
        Try
            ds = New DataSet()
            MyBase.sql = "SELECT ID, TenTour, GioiThieu, DonGia, NgayBD, NgayKT " & _
                        "FROM TOUR " & _
                        "WHERE NgayBD >= " & IIf(ngaybd = DateTime.MinValue, "NgayBD", "'" & ngaybd & "'") & " And " & _
                            "NgayKT <= " & IIf(ngaykt = DateTime.MinValue, "NgayKT", "'" & ngaykt & "'")
            MyBase.InitializeCommand()
            MyBase.FillDataSet(ds, "TOUR")
            Return ds
        Catch e As System.Exception
            Throw New Exception(e.Message, e.InnerException)
        Finally
            MyBase.CleanUpObject()
        End Try
    End Function

    Public Function ThongKeKhachHang(ByVal ngaybd As DateTime, ByVal ngaykt As DateTime) As DataSet
        Dim ds As DataSet
        Try
            ds = New DataSet()
            MyBase.sql = "SELECT t.ID, TenTour, TenKH " & _
                        "FROM TOUR t, PHIEUDATTOUR p, CHITIET_PHIEUDATTOUR ct, KHACHHANG kh " & _
                        "WHERE ct.IDKhachHang = kh.ID And t.ID = p.IDTour And p.ID = ct.IDPhieuDatTour And NgayLapPhieu >= " & IIf(ngaybd = DateTime.MinValue, "NgayLapPhieu", "'" & ngaybd & "'") & " And " & _
                            "NgayLapPhieu <= " & IIf(ngaykt = DateTime.MinValue, "NgayLapPhieu", "'" & ngaykt & "'")
            MyBase.InitializeCommand()
            MyBase.FillDataSet(ds, "KHACHHANG")
            Return ds
        Catch e As System.Exception
            Throw New Exception(e.Message, e.InnerException)
        Finally
            MyBase.CleanUpObject()
        End Try
    End Function

	Public Function Insert(obj As TOURDTO) As Boolean
		Try
			MyBase.sql = "usp_TOUR_Insert"
			MyBase.InitializeCommand()
			MyBase.AddParameter("@TenTour", SqlDbType.NVarChar, 1000, obj.TenTour)
			MyBase.AddParameter("@GioiThieu", SqlDbType.NVarChar, 1000, obj.GioiThieu)
            MyBase.AddParameter("@DonGia", SqlDbType.BigInt, 1000, obj.DonGia)
            MyBase.AddParameter("@NgayBD", SqlDbType.DateTime, 1000, obj.NgayBD)
            MyBase.AddParameter("@NgayKT", SqlDbType.DateTime, 1000, obj.NgayKT)
			Return ExecuteStoredProcedure()
		Catch
			Return False
		End Try
	End Function

	Public Function Update(obj As TOURDTO) As Boolean
		Try
			MyBase.sql = "usp_TOUR_Update"
			MyBase.InitializeCommand()
			MyBase.AddParameter("@ID", SqlDbType.Int, 1000, obj.ID)
			MyBase.AddParameter("@TenTour", SqlDbType.NVarChar, 1000, obj.TenTour)
			MyBase.AddParameter("@GioiThieu", SqlDbType.NVarChar, 1000, obj.GioiThieu)
            MyBase.AddParameter("@DonGia", SqlDbType.BigInt, 1000, obj.DonGia)
            MyBase.AddParameter("@NgayBD", SqlDbType.DateTime, 1000, obj.NgayBD)
            MyBase.AddParameter("@NgayKT", SqlDbType.DateTime, 1000, obj.NgayKT)
			Return ExecuteStoredProcedure()
		Catch
			Return False
		End Try
	End Function

	Public Function Delete(id As Integer) As Boolean
		Try
			MyBase.sql = "usp_TOUR_Delete"
			MyBase.InitializeCommand()
			MyBase.AddParameter("@ID", SqlDbType.Int, 1000, id)
			Return ExecuteStoredProcedure()
		Catch
			Return False
		End Try
	End Function
End Class
